﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data

Namespace SV_Global.SQL
    Public Class StoredProcedures

#Region "Declaração de Variáveis"
        Private Shared ConexaoSQL As New SqlConnection(SV_Global.Global.Configuracoes.StringConexao)
#End Region

#Region "Funções"

        ''' <summary>
        ''' Executa Stored Procedure recebida como parâmetro - RETORNA DATAREADER
        ''' </summary>
        ''' <param name="strSP"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overloads Shared Function retornaDataReader(ByVal strSP As String) As SqlDataReader

            Dim ConexaoSQL = New SqlConnection(SV_Global.Global.Configuracoes.StringConexao)
            ConexaoSQL.Open()

            ' Cria DataReader
            Dim DataReader As SqlDataReader

            ' Cria um objeto SQLCommand a partir da consulta
            Dim consulta As New SqlCommand(strSP, ConexaoSQL)
            consulta.CommandType = CommandType.Text

            ' Executa a Consulta
            DataReader = consulta.ExecuteReader(CommandBehavior.CloseConnection)

            ' Fecha a Conexão SQL
            consulta.Dispose()
            ConexaoSQL.Close()

            ' Retorna o DataReader
            Return DataReader
        End Function

        ''' <summary>
        ''' Executa Stored Procedure, utilizando os parâmetros recebidos - RETORNA DATAREADER
        ''' </summary>
        ''' <param name="strSP"></param>
        ''' <param name="cmdParametros"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overloads Shared Function retornaDataReader(ByVal strSP As String, ByVal cmdParametros As ArrayList) As SqlDataReader
            Dim ConexaoSQL = New SqlConnection(SV_Global.Global.Configuracoes.StringConexao)
            ConexaoSQL.Open()

            ' Cria DataReader
            Dim DataReader As SqlDataReader

            ' Cria um objeto SQLCommand a partir da Stored Procedure
            Dim consulta As New SqlCommand(strSP, ConexaoSQL)
            consulta.CommandType = CommandType.StoredProcedure

            ' Define os parâmetros
            Dim par As SqlParameter
            ' Percorre a coleção de paremetros
            For Each par In cmdParametros
                consulta.Parameters.Add(par)
            Next

            ' Executa a Consulta
            DataReader = consulta.ExecuteReader()

            ' Fecha a Conexão SQL
            consulta.Dispose()

            ' Retorna o DataReader
            Return DataReader
        End Function

        ''' <summary>
        ''' Executa Stored Procedure recebida como parâmetro - RETORNA DATASET
        ''' </summary>
        ''' <param name="strSP"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overloads Shared Function retornaDataSet(ByVal strSP As String) As DataSet

            Dim ConexaoSQL = New SqlConnection(SV_Global.Global.Configuracoes.StringConexao)
            ConexaoSQL.Open()

            ' Cria DataSet
            Dim DataSet As New DataSet

            ' Cria um objeto SQLDataAdapter a partir da consulta
            Dim DataAdapter As New SqlDataAdapter(strSP, ConexaoSQL)
            DataAdapter.SelectCommand.CommandType = CommandType.Text

            ' Preenche o DataSet
            DataAdapter.Fill(DataSet)

            ' Fecha a Conexão SQL
            DataAdapter.Dispose()
            ConexaoSQL.Close()

            ' Retorna o DataSet
            Return DataSet
        End Function

        ''' <summary>
        ''' Executa Stored Procedure, utilizando os parâmetros recebidos - RETORNA DATASET
        ''' </summary>
        ''' <param name="strSP"></param>
        ''' <param name="cmdParametros"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overloads Shared Function retornaDataSet(ByVal strSP As String, ByVal cmdParametros As ArrayList) As DataSet
            Dim ConexaoSQL = New SqlConnection(SV_Global.Global.Configuracoes.StringConexao)
            ConexaoSQL.Open()

            ' Cria DataSet
            Dim DataSet As New DataSet

            ' Cria um objeto SQLDataAdapter a partir da consulta
            Dim DataAdapter As New SqlDataAdapter(strSP, ConexaoSQL)
            DataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

            ' Define os parâmetros
            Dim par As SqlParameter
            ' Percorre a coleção de paremetros
            For Each par In cmdParametros
                DataAdapter.SelectCommand.Parameters.Add(par)
                par.Direction = ParameterDirection.Input
            Next

            ' Preenche o DataSet
            DataAdapter.Fill(DataSet, strSP)

            ' Fecha a Conexão SQL
            ConexaoSQL.Close()
            DataAdapter.Dispose()

            ' Retorna o DataSet
            Return DataSet
        End Function


        ''' <summary>
        ''' Executa Stored Procedure Ou Sql Query, utilizando os parâmetros recebidos - RETORNA DATASET
        ''' </summary>
        Public Overloads Shared Function retornaDataSet(ByVal strSP As String, ByVal cmdParametros As ArrayList, ByVal comandoTipo As CommandType) As DataSet
            Dim ConexaoSQL = New SqlConnection(SV_Global.Global.Configuracoes.StringConexao)
            ConexaoSQL.Open()

            ' Cria DataSet
            Dim DataSet As New DataSet

            ' Cria um objeto SQLDataAdapter a partir da consulta
            Dim DataAdapter As New SqlDataAdapter(strSP, ConexaoSQL)

            'Atribui o comando recebido
            DataAdapter.SelectCommand.CommandType = comandoTipo

            ' Define os parâmetros
            Dim par As SqlParameter
            ' Percorre a coleção de paremetros
            For Each par In cmdParametros
                DataAdapter.SelectCommand.Parameters.Add(par)
                par.Direction = ParameterDirection.Input
            Next

            ' Preenche o DataSet
            DataAdapter.Fill(DataSet, strSP)

            ' Fecha a Conexão SQL
            ConexaoSQL.Close()
            DataAdapter.Dispose()

            ' Retorna o DataSet
            Return DataSet
        End Function


        ''' <summary>
        ''' Executa Stored Procedure recebida como parâmetro - RETORNA DATATABLE
        ''' </summary>
        ''' <param name="strSP"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overloads Shared Function retornaDataTable(ByVal strSP As String) As DataTable
            Dim ConexaoSQL = New SqlConnection(SV_Global.Global.Configuracoes.StringConexao)
            ConexaoSQL.Open()

            ' Cria DataSet
            Dim DataTable As New DataTable

            ' Cria um objeto SQLDataAdapter a partir da consulta
            Dim DataAdapter As New SqlDataAdapter(strSP, ConexaoSQL)
            DataAdapter.SelectCommand.CommandType = CommandType.Text

            ' Preenche o DataTable
            DataAdapter.Fill(DataTable)

            ' Fecha a Conexão SQL
            DataAdapter.Dispose()
            ConexaoSQL.Close()

            ' Retorna o DataTable
            Return DataTable
        End Function

        ''' <summary>
        ''' Executa Stored Procedure recebida como parâmetro - RETORNA DATAREADER
        ''' </summary>
        ''' <param name="strSP"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overloads Shared Function executaProcedure(ByVal strSP As String) As Int32
            Dim ConexaoSQL = New SqlConnection(SV_Global.Global.Configuracoes.StringConexao)
            ConexaoSQL.Open()

            ' Cria DataReader
            Dim DataReader As SqlDataReader

            ' Cria um objeto SQLCommand a partir da consulta
            Dim consulta As New SqlCommand(strSP, ConexaoSQL)
            consulta.CommandType = CommandType.Text

            ' Executa a Consulta
            Dim qtdRegistrosAfetados As Integer = consulta.ExecuteNonQuery()

            ' Fecha a Conexão SQL
            consulta.Dispose()
            ConexaoSQL.Close()

            ' Retorna o DataReader
            Return qtdRegistrosAfetados
        End Function

        ''' <summary>
        ''' Executa Stored Procedure recebida como parâmetro - RETORNA DATAREADER
        ''' </summary>
        ''' <param name="strSP"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overloads Shared Function executaProcedure(ByVal strSP As String, ByVal cmdParametros As ArrayList) As Int32
            Dim ConexaoSQL = New SqlConnection(SV_Global.Global.Configuracoes.StringConexao)
            ConexaoSQL.Open()

            ' Cria DataReader
            Dim DataReader As SqlDataReader

            ' Cria um objeto SQLCommand a partir da consulta
            Dim consulta As New SqlCommand(strSP, ConexaoSQL)
            consulta.CommandType = CommandType.StoredProcedure

            ' Define os parâmetros
            Dim par As SqlParameter
            ' Percorre a coleção de paremetros
            For Each par In cmdParametros
                consulta.Parameters.Add(par)
                par.Direction = ParameterDirection.Input
            Next

            ' Executa a Consulta
            Dim qtdRegistrosAfetados As Integer = consulta.ExecuteNonQuery()

            ' Fecha a Conexão SQL
            consulta.Dispose()
            ConexaoSQL.Close()

            ' Retorna o DataReader
            Return qtdRegistrosAfetados
        End Function

#End Region

    End Class
End Namespace


